package org.compiere.process;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;

import org.compiere.model.I_AGR_StageList;
import org.compiere.model.MAGRStage;
import org.compiere.model.MAGRStageList;
import org.compiere.model.MBPartner;
import org.compiere.model.MBSCCard;
import org.compiere.model.MTable;
import org.compiere.model.MUser;
import org.compiere.model.PO;
import org.compiere.model.Query;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class BSCSendMail extends SvrProcess {

	
	private int AGR_Stage_ID = 0;
	private int AD_Table_ID = 0;
	private int Record_ID = 0;
	
	private String Name  = null;
	private String DocumentNo = null;
	private int AD_Client_ID = 0 ; // Env.getAD_Client_ID(getCtx());
	private int AD_Org_ID = 0; // Env.getAD_Org_ID(getCtx());
	private String resultIt = null;
	private PO po = null; 
	private MAGRStage stage = null;
	private boolean IsApproved = false;
	private String IsBack = "N";
	
	@Override
	protected void prepare() {
		AD_Client_ID = Env.getAD_Client_ID(getCtx());
		AD_Org_ID = Env.getAD_Org_ID(getCtx());
		initParameters();
		IsBack = (IsApproved ? "N": "Y");
		if (AD_Table_ID > 0 && Record_ID > 0) {
	        MTable table = MTable.get(getCtx(), AD_Table_ID);
	        log.info("Table = " + table);
	        po = table.getPO (Record_ID, get_TrxName());
		}
		if (AGR_Stage_ID > 0) {
			stage = getNextStage();
		}

	}

	@Override
	protected String doIt() throws Exception {
		String resultMsg = "";
		if (po != null && stage != null) {
			resultMsg = sendMail(getBPartners());
		}
		log.log(Level.WARNING, "Msg. send for : "+resultMsg);
		return null;
	}

	private String sendMail(MBPartner[] partners) {
		String sendList = "";
		if (partners != null) {
			for(MBPartner partner:partners) {
				int AD_User_ID = getAD_User_ID(partner.getC_BPartner_ID());
				if (AD_User_ID > 0) {
					//info text
					sendList = sendList+partner.getName();
					//
					String textMail = partner.getName() + "\n" 
					         + "Документ: "+ getDocName() +"\n"
					         +"№ "+ getDocumentNo() + "\n"
		                     + "Статус: " + getStageName() + "\n"
		                     + "---------------------------------\n"
		                     + (IsApproved ? getStageDescription() : getNotApprovedDescription());
					String textLabel = "Уведомление ("+getDocName()+" № "+getDocumentNo()+")";
			
					System.out.println("==============================================");
					System.out.println(textLabel);
					System.out.println(textMail);
					System.out.println("==============================================");
			
					try {
						Util.sendMail(AD_User_ID, getAD_User_ID(), textLabel, textMail, false);
					} catch(Exception e) {
						log.log(Level.SEVERE,"BSCSendMail: ",e);
					}
				}
			}
		}
		return sendList;
	}

	private MBPartner[] getBPartners() {
		String whereClause ="1=1";
		MAGRStage stage = new MAGRStage(getCtx(), AGR_Stage_ID, get_TrxName());
		stage.getAGR_Stage_ID();
		MAGRStage nextStage = stage.getNextStage();
		nextStage.getAGR_Stage_ID();
		MAGRStage prevStage = null;
		if(!stage.getStageType().equals(MAGRStage.STAGETYPE_Initial))
			prevStage = stage.getPreviousStage();
		
		if(IsApproved)
		{
			whereClause = "C_BPartner_ID IN(SELECT C_BPartner_ID FROM AGR_StageListLine WHERE IsActive='Y'"+ 
					"\nAND AGR_StageList_ID IN(SELECT AGR_StageList_ID FROM AGR_StageList WHERE IsActive='Y'"+ 
					"\nAND AGR_Stage_ID="+AGR_Stage_ID+" AND C_BPartner_ID = "+new MUser(getCtx(), getAD_User_ID(), get_TrxName()).getC_BPartner_ID()+"))";
			
			DB.executeUpdate("UPDATE BSC_Card SET FromAD_User_ID = "+Env.getAD_User_ID(getCtx())+" WHERE BSC_Card_ID = "+po.get_ID(), get_TrxName());
		}
		else
		{
			whereClause = "C_BPartner_ID IN(SELECT C_BPartner_ID FROM AGR_StageList WHERE AGR_StageList_ID IN"+
					"\n(SELECT AGR_StageList_ID FROM AGR_StageListLine WHERE IsActive='Y' AND AGR_StageList_ID IN"+
					"\n(SELECT AGR_StageList_ID FROM AGR_StageList WHERE IsActive='Y' AND AGR_Stage_ID="+prevStage.getAGR_Stage_ID()+")"+
					"\nAND C_BPartner_ID="+new MUser(getCtx(), getAD_User_ID(), get_TrxName()).getC_BPartner_ID()+"))";
			//TODO Remove this code block after test 
			if(getAD_User_ID()==1000011)
			whereClause = whereClause+"\nAND C_BPartner_ID="+new MBSCCard(getCtx(), Record_ID, get_TrxName()).getFromAD_User().getC_BPartner_ID();
			//
		}
		List<MBPartner> list = new Query(Env.getCtx(), MBPartner.Table_Name,whereClause,get_TrxName()).list();

		String tempSQL = "UPDATE BSC_Card SET toAD_User_ID = (SELECT AD_User_ID FROM AD_User WHERE C_BPartner_ID = "+
				list.toArray(new MBPartner[list.size()])[0].getC_BPartner_ID()+") WHERE BSC_Card_ID = "+po.get_ID();
		DB.executeUpdate(tempSQL, get_TrxName());
		return list.toArray(new MBPartner[list.size()]);
	}

	private void initParameters() {
		for(ProcessInfoParameter param: getParameter()) {
			if (param.getParameterName().equals("AGR_Stage_ID")) {
				AGR_Stage_ID = Integer.parseInt(param.getParameter().toString());
			} else if(param.getParameterName().equals("AD_Table_ID")) {
				AD_Table_ID = Integer.parseInt(param.getParameter().toString());
			} else if(param.getParameterName().equals("Record_ID")) {
				Record_ID = Integer.parseInt(param.getParameter().toString());
			} else if (param.getParameterName().equals("IsApproved")) {
				IsApproved = Boolean.parseBoolean(param.getParameter().toString());
			}
		}
	}

	private int getAD_User_ID(int C_BPartner_ID) {
		int result = 0;
		String sql = "SELECT AD_User_ID FROM AD_User WHERE C_BPartner_ID = ?";
		result = DB.getSQLValue(get_TrxName(), sql, C_BPartner_ID);
		return result;
	}
	
	private String getStageName() {
		return (stage == null ? "" : stage.getName());
	}
	
	private String getStageDescription() {
		return (stage == null ? "" : stage.getDescription());
	}
	
	private String getDocName() {
		String result = po.get_Value("Name").toString();
		return (result == null ? "" : result);
	}
	
	private String getDocumentNo() {
		String result = po.get_Value("DocumentNo").toString();
		return (result == null ? "" : result);
	}
	
	private MAGRStage getNextStage() {
		String sql = "SELECT * FROM AGR_Stage WHERE AGR_Stage_ID IN (SELECT AGR_NextStage_ID FROM AGR_Node WHERE IsBack = '"+IsBack+"' AND AGR_Stage_ID = ?)";
		MAGRStage result = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		try {
			pstmt = DB.prepareStatement(sql,get_TrxName());
			pstmt.setInt(1, AGR_Stage_ID);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = new MAGRStage(Env.getCtx(),rs,get_TrxName());
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "product", e);
		} finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		return result;
	}
	
	private String getNotApprovedDescription() {
		String result = "";
		String sql = "select Description from AGR_AgreementList where Record_ID = ? AND AGR_Stage_ID = ? and AD_Table_ID = ? and sign_n = 'Y' and sign_y = 'N'";
		result = DB.getSQLValueString(get_TrxName(), sql, new Integer(Record_ID), new Integer(AGR_Stage_ID),new Integer(AD_Table_ID));
		return result;
	}
	private MAGRStageList[] getAGRStageList(int AGR_Stage_ID){
		List<MAGRStageList> list = new Query(getCtx(), I_AGR_StageList.Table_Name, "IsActive = 'Y' AND AGR_Stage_ID = ?", get_TrxName()).setParameters(AGR_Stage_ID).list();
		return list.toArray(new MAGRStageList[list.size()]);
	}
}
//String whereClause =" C_BPartner_ID IN (SELECT C_BPartner_ID FROM AGR_StageList"+ 
//"\nWHERE IsActive='Y' AND AGR_Stage_ID IN (SELECT AGR_NextStage_ID FROM AGR_Node WHERE AGR_Stage_ID = "+AGR_Stage_ID+")"+
//"\nAND AGR_StageList_ID IN(SELECT AGR_StageList_ID FROM AGR_StageListLine WHERE AGR_StageList_ID IN"+ 
//	"\n(SELECT AGR_StageList_ID FROM AGR_StageList WHERE AGR_Stage_ID IN"+ 
//		"\n(SELECT AGR_NextStage_ID FROM AGR_Node WHERE IsBack = '"+IsBack+"' AND AGR_Stage_ID = "+AGR_Stage_ID+"))))";
//String whereClause = "C_BPartner_ID IN (\n"+
//"with t1 as (\n"+
//"SELECT AGR_StageList_ID, C_BPartner_ID \n"+
//"FROM AGR_StageList \n"+
//"WHERE AGR_Stage_ID IN (SELECT AGR_NextStage_ID FROM AGR_Node WHERE IsBack = '"+IsBack+"' AND AGR_Stage_ID = "+AGR_Stage_ID+")\n"+
//"), t2 as (\n"+
//"SELECT AGR_StageList_ID,C_BPartner_ID FROM AGR_StageList WHERE AGR_Stage_ID = "+AGR_Stage_ID+"\n"+
//"), t3 as (\n"+
//"SELECT C_BPartner_ID from AD_User where AD_User_ID = "+ po.getUpdatedBy()+"\n"+
//"), t4 as (\n"+
//"SELECT * FROM t1 WHERE t1.C_BPartner_ID IN (SELECT C_BPartner_ID FROM t3)\n"+
//"), t5 as (\n"+
//"SELECT * FROM AGR_StageListLine WHERE AGR_STageList_ID in (SELECT AGR_STageList_ID FROM t4) \n"+
//") \n"+
//"SELECT C_BPartner_ID from t5)";